﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Mulberry.Model;
using OMG.Fill.Mulberry.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Mulberry.Service
{
    /// <summary>
    /// 表10县生产信息调查统计表T_MulProdInfoChart通用操作类
    /// </summary>
    public class MulProdInfoChartServices:MulProdInfoCharts
    {
        ReportServices rs = new ReportServices();
        public Pager<List<MulProdInfoChartEntity>> GetPageList(MulProdInfoChartQuery query)
        {
            Pager<List<MulProdInfoChartEntity>> obj = new Pager<List<MulProdInfoChartEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_MulProdInfoChart where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<MulProdInfoChartEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表10县生产信息调查统计表T_MulProdInfoChart分页列表失败", ex);
                return obj;
            }
        }


        /// <summary>
        /// 获取填报数据
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns></returns>
        public ResResult<List<MulProdInfoChartEntity>> GetInfo(string reportId)
        {
            ResResult<List<MulProdInfoChartEntity>> obj = new ResResult<List<MulProdInfoChartEntity>>();
            //判定是否生成过报表
            if (CheckRepCount(reportId))
            {
                //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                List<MulProdInfoChartEntity> templist = GetTabContent(reportId, 0);
                try
                {
                    if (AddInfo(templist) > 0)
                    {
                        obj.model = templist;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "初始化并且返回数据成功！";
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表10县生产信息调查统计表T_MulProdInfoChart对象信息失败", ex);
                    obj.message = "返回数据失败！";
                }
            }
            else
            {
                try
                {
                    //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                    List<MulProdInfoChartEntity> templist = GetTabContent(reportId, 1);
                    obj.model = templist;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "返回数据成功！";
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表10县生产信息调查统计表T_MulProdInfoChart对象信息失败", ex);
                    obj.message = "返回数据失败！";
                }
            }
            return obj;
        }


        /// <summary>
        /// 初始化报表内容
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int AddInfo(List<MulProdInfoChartEntity> list)
        {
            string strSql = @"INSERT INTO dbo.T_MulProdInfoChart";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "NumTownship,";
            strSql += "NumTownshipName,";
            strSql += "NumVillage,";
            strSql += "NumVillageName,";
            strSql += "MulHouseholds,";
            strSql += "AnnualSilkwormOne,";
            strSql += "AnnualSilkwormTwo,";
            strSql += "AnnualSilkwormThree,";
            strSql += "AnnualSilkwormFour,";
            strSql += "TecPersonTotalNum,";
            strSql += "TecPersonAllInNum,";
            strSql += "ManNum,";
            strSql += "WomanNum,";
            strSql += "AgeOne,";
            strSql += "AgeTwo,";
            strSql += "AgeThree,";
            strSql += "AgeFour,";
            strSql += "EduLevelOne,";
            strSql += "EduLeveTwo,";
            strSql += "EduLevelThree,";
            strSql += "EduLevelFour,";
            strSql += "TitleOne,";
            strSql += "TitleTwo,";
            strSql += "TitleThree,";
            strSql += "TitleFour,";
            strSql += "IncuRoomNum,";
            strSql += "IncuRoomArea,";
            strSql += "JiCuiqingPower,";
            strSql += "YearsOfIncu,";
            strSql += "SilkwormSpots,";
            strSql += "SilkwormArea,";
            strSql += "QuaternaryFertility,";
            strSql += "AnnualTotalFertility,";
            strSql += "LargeCocoonNum,";
            strSql += "LargeCocoonArea,";
            strSql += "LaborSavingRearTable,";
            strSql += "GridCluster,";
            strSql += "CocoonStation,";
            strSql += "CocoonStationArea,";
            strSql += "SeasonalDryCapacity,";
            strSql += "CircularDryMachine)";
            //strSql += "ReelingEnterprise,";
            //strSql += "ReelingCapacityOne,";
            //strSql += "ReelingCapacityTwo,";
            //strSql += "ReelingCapacityThree,";
            //strSql += "ReelingCapacityFour,";
            //strSql += "SilkwormField,";
            //strSql += "ThroughPut,";
            //strSql += "SilkEnterprise,";
            //strSql += "ProcessedSilkQuilt,";
            //strSql += "Other,";
            //strSql += "OtherThroughPut)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@NumTownship,";
            strSql += "@NumTownshipName,";
            strSql += "@NumVillage,";
            strSql += "@NumVillageName,";
            strSql += "@MulHouseholds,";
            strSql += "@AnnualSilkwormOne,";
            strSql += "@AnnualSilkwormTwo,";
            strSql += "@AnnualSilkwormThree,";
            strSql += "@AnnualSilkwormFour,";
            strSql += "@TecPersonTotalNum,";
            strSql += "@TecPersonAllInNum,";
            strSql += "@ManNum,";
            strSql += "@WomanNum,";
            strSql += "@AgeOne,";
            strSql += "@AgeTwo,";
            strSql += "@AgeThree,";
            strSql += "@AgeFour,";
            strSql += "@EduLevelOne,";
            strSql += "@EduLeveTwo,";
            strSql += "@EduLevelThree,";
            strSql += "@EduLevelFour,";
            strSql += "@TitleOne,";
            strSql += "@TitleTwo,";
            strSql += "@TitleThree,";
            strSql += "@TitleFour,";
            strSql += "@IncuRoomNum,";
            strSql += "@IncuRoomArea,";
            strSql += "@JiCuiqingPower,";
            strSql += "@YearsOfIncu,";
            strSql += "@SilkwormSpots,";
            strSql += "@SilkwormArea,";
            strSql += "@QuaternaryFertility,";
            strSql += "@AnnualTotalFertility,";
            strSql += "@LargeCocoonNum,";
            strSql += "@LargeCocoonArea,";
            strSql += "@LaborSavingRearTable,";
            strSql += "@GridCluster,";
            strSql += "@CocoonStation,";
            strSql += "@CocoonStationArea,";
            strSql += "@SeasonalDryCapacity,";
            strSql += "@CircularDryMachine)";
            //strSql += "@ReelingEnterprise,";
            //strSql += "@ReelingCapacityOne,";
            //strSql += "@ReelingCapacityTwo,";
            //strSql += "@ReelingCapacityThree,";
            //strSql += "@ReelingCapacityFour,";
            //strSql += "@SilkwormField,";
            //strSql += "@ThroughPut,";
            //strSql += "@SilkEnterprise,";
            //strSql += "@ProcessedSilkQuilt,";
            //strSql += "@Other,";
            //strSql += "@OtherThroughPut)";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                try
                {
                    int result = dbCon.Execute(strSql, list);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.SuccessResult;
                }
                catch (Exception ex)
                {
                    LogHelper.ErrorLog("新增表10县生产信息调查统计表T_MulProdInfoChart对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.ErrorResult;

                }
            }
        }

        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_MulProdInfoChart where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取表10县生产信息调查统计表T_MulProdInfoChart分页列表失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 获取报表内容
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="edittype">0的时候返回视图列表，不为0的时候返回数据库列表</param>
        /// <returns></returns>
        public List<MulProdInfoChartEntity> GetTabContent(string reportId, int edittype)
        {
            List<MulProdInfoChartEntity> obj = new List<MulProdInfoChartEntity>();
            string strSql = "";
            if (edittype == 0)
            {
                //strSql = @"select * from dbo.V_MulProdInfoChart order by SortCode asc,ItemCode asc";
                strSql = @"select * from dbo.T_MulProdInfoChart order by SortCode asc,ItemCode asc";
            }
            else
            {
                strSql = @"select * from dbo.T_MulProdInfoChart where ReportId='" + reportId + "' order by SortCode asc,ItemCode asc";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj = dbCon.Query<MulProdInfoChartEntity>(strSql).ToList();
                    if (edittype == 0)
                    {
                        foreach (var item in obj)
                        {
                            item.ReportId = reportId;
                            item.Id = CustomFun.GetGuid();
                        }
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取表10县生产信息调查统计表T_MulProdInfoChart分页列表失败", ex);
                return obj;
            }
        }


        /// <summary>
        /// 获取指定对象
        /// </summary>
        public override ResResult<MulProdInfoChartEntity> GetModel(string ReportId)
        {
            ResResult<MulProdInfoChartEntity> obj = new ResResult<MulProdInfoChartEntity>();
            MulProdInfoChartEntity model = new MulProdInfoChartEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                if (AddInfo(model).status > 0)
                {
                    obj.model = model;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_MulProdInfoChart where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<MulProdInfoChartEntity>(strSql, new { ReportId = ReportId }).FirstOrDefault();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取表1桑园面积统计表T_MulAreaStatist对象信息失败", ex);

                }
            }
            return obj;
        }

        /// <summary>
        /// 修改
        /// </summary>
        public override ResResult<int> UpdateInfo(MulProdInfoChartEntity model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_MulProdInfoChart set ";
            strSql += "ReportId = @ReportId,";
            strSql += "NumTownship = @NumTownship,";
            strSql += "NumTownshipName = @NumTownshipName,";
            strSql += "NumVillage = @NumVillage,";
            strSql += "NumVillageName = @NumVillageName,";
            strSql += "MulHouseholds = @MulHouseholds,";
            strSql += "AnnualSilkwormOne = @AnnualSilkwormOne,";
            strSql += "AnnualSilkwormTwo = @AnnualSilkwormTwo,";
            strSql += "AnnualSilkwormThree = @AnnualSilkwormThree,";
            strSql += "AnnualSilkwormFour = @AnnualSilkwormFour,";
            strSql += "TecPersonTotalNum = @TecPersonTotalNum,";
            strSql += "TecPersonAllInNum = @TecPersonAllInNum,";
            strSql += "ManNum = @ManNum,";
            strSql += "WomanNum = @WomanNum,";
            strSql += "AgeOne = @AgeOne,";
            strSql += "AgeTwo = @AgeTwo,";
            strSql += "AgeThree = @AgeThree,";
            strSql += "AgeFour = @AgeFour,";
            strSql += "EduLevelOne = @EduLevelOne,";
            strSql += "EduLeveTwo = @EduLeveTwo,";
            strSql += "EduLevelThree = @EduLevelThree,";
            strSql += "EduLevelFour = @EduLevelFour,";
            strSql += "TitleOne = @TitleOne,";
            strSql += "TitleTwo = @TitleTwo,";
            strSql += "TitleThree = @TitleThree,";
            strSql += "TitleFour = @TitleFour,";
            strSql += "IncuRoomNum = @IncuRoomNum,";
            strSql += "IncuRoomArea = @IncuRoomArea,";
            strSql += "JiCuiqingPower = @JiCuiqingPower,";
            strSql += "YearsOfIncu = @YearsOfIncu,";
            strSql += "SilkwormSpots = @SilkwormSpots,";
            strSql += "SilkwormArea = @SilkwormArea,";
            strSql += "QuaternaryFertility = @QuaternaryFertility,";
            strSql += "AnnualTotalFertility = @AnnualTotalFertility,";
            strSql += "LargeCocoonNum = @LargeCocoonNum,";
            strSql += "LargeCocoonArea = @LargeCocoonArea,";
            strSql += "LaborSavingRearTable = @LaborSavingRearTable,";
            strSql += "GridCluster = @GridCluster,";
            strSql += "CocoonStation = @CocoonStation,";
            strSql += "CocoonStationArea = @CocoonStationArea,";
            strSql += "SeasonalDryCapacity = @SeasonalDryCapacity,";
            strSql += "CircularDryMachine = @CircularDryMachine";
            //strSql += "ReelingEnterprise = @ReelingEnterprise,";
            //strSql += "ReelingCapacityOne = @ReelingCapacityOne,";
            //strSql += "ReelingCapacityTwo = @ReelingCapacityTwo,";
            //strSql += "ReelingCapacityThree = @ReelingCapacityThree,";
            //strSql += "ReelingCapacityFour = @ReelingCapacityFour,";
            //strSql += "SilkwormField = @SilkwormField,";
            //strSql += "ThroughPut = @ThroughPut,";
            //strSql += "SilkEnterprise = @SilkEnterprise,";
            //strSql += "ProcessedSilkQuilt = @ProcessedSilkQuilt,";
            //strSql += "Other = @Other,";
            //strSql += "OtherThroughPut = @OtherThroughPut";
            strSql += " where Id = @Id";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();
                try
                {
                    //if (list.Count > 0)
                    //{
                    //    string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                    //    dbCon.Execute(strRep, new { ReportId = list[0].ReportId, UpdateTime = DateTime.Now }, trans);
                    //}
                    //int result = dbCon.Execute(strSql, list, trans);
                    int result = dbCon.Execute(strSql, model, trans);
                    string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                    dbCon.Execute(strRep, new { ReportId = model.ReportId, UpdateTime = DateTime.Now }, trans);
                    //提交事务
                    trans.Commit();
                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "更新成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    trans.Rollback();
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("更新表10县生产信息调查统计表T_MulProdInfoChart对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
        }

        /// <summary>
        /// 获取今往年数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<MulProdViewModel> GetModel(MulNowModel model)
        {
            ResResult<MulProdViewModel> obj = new ResResult<MulProdViewModel>();
            MulProdInfoChartEntity vfEntity = new MulProdInfoChartEntity();
            MulProdViewModel vvm = new MulProdViewModel();
            DictServices dictServices = new DictServices();
            var paramlist = new DynamicParameters();
            string strSqlReport = "";
            if (model != null)
            {
                if (!string.IsNullOrEmpty(model.AreaCode) && !string.IsNullOrEmpty(model.ReportCode) && !string.IsNullOrEmpty(model.FillInYear))
                {
                    string FillInYear = dictServices.GetKey(model.FillInYear);
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("ReportCode", model.ReportCode);
                    paramlist.Add("FillInYear", FillInYear);

                    strSqlReport = "SELECT * FROM T_Report r LEFT JOIN T_MulProdInfoChart mc on mc.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @FillInYear and AreaCode = @AreaCode ";

                    Boolean currentResult = rs.CheckRepCreate(model.ReportCode, model.AreaCode, FillInYear);
                    var dbCon = ConnectFactory.GetOpenConnection();
                    //获取报表Id
                    vfEntity.ReportId = rs.GetReportId(model.ReportCode, model.AreaCode, FillInYear);
                    //判断表T_VegFacility是否初始化过
                    if (CheckRepCount(vfEntity.ReportId))
                    {
                        if (AddInfo(vfEntity).status > 0)//报表初始化
                        {
                            //查询去年数据
                            obj.model = dbCon.Query<MulProdViewModel>(strSqlReport, paramlist).FirstOrDefault();
                            obj.status = 1;//初始化成功status为1
                            obj.message = "MulProdInfoChart报表初始化成功！";
                            return obj;
                        }
                    }
                    else
                    {
                        try
                        {
                            obj.model = dbCon.Query<MulProdViewModel>(strSqlReport, paramlist).FirstOrDefault();
                            obj.status = 2;//成功状态为2
                            obj.message = "获取MulProdInfoChart报表今往年数据成功！";
                            return obj;
                        }
                        catch (Exception ex)
                        {
                            obj.status = 0; //失败状态
                            obj.message = ex.ToString().Trim();
                            LogHelper.ErrorLog("获取MulProdInfoChart报表今往年数据失败", ex);
                        }
                    }
                }
            }
            return obj;
        }

        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<MulProdInfoModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<MulProdInfoModel> list = new List<MulProdInfoModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                MulProdInfoModel model = new MulProdInfoModel();
                IRow row3 = sheet.GetRow(3);
                if (row3.GetCell(1) != null && row3.GetCell(1).ToString() != "")
                {
                    model.NumTownship = row3.GetCell(1).ToString();
                }
                else
                {
                    model.NumTownship = null;
                }
                if (row3.GetCell(3) != null && row3.GetCell(3).ToString() != "")
                {
                    model.NumTownshipName = row3.GetCell(3).ToString();
                }
                else
                {
                    model.NumTownshipName = null;
                }
                IRow row4 = sheet.GetRow(4);
                if (row4.GetCell(1) != null && row4.GetCell(1).ToString() != "")
                {
                    model.NumVillage = row4.GetCell(1).ToString();
                }
                else
                {
                    model.NumTownship = null;
                }
                if (row4.GetCell(3) != null && row4.GetCell(3).ToString() != "")
                {
                    model.NumVillageName = row4.GetCell(3).ToString();
                }
                else
                {
                    model.NumTownshipName = null;
                }
                IRow row5 = sheet.GetRow(5);
                if (row5.GetCell(1) != null && row5.GetCell(1).ToString() != "")
                {
                    model.MulHouseholds = row5.GetCell(1).ToString();
                }
                else
                {
                    model.MulHouseholds = null;
                }
                IRow row6 = sheet.GetRow(6);
                if (row6.GetCell(1) != null && row6.GetCell(1).ToString() != "")
                {
                    model.AnnualSilkwormOne = row6.GetCell(1).ToString();
                }
                else
                {
                    model.AnnualSilkwormOne = null;
                }
                if (row6.GetCell(3) != null && row6.GetCell(3).ToString() != "")
                {
                    model.AnnualSilkwormTwo = row6.GetCell(3).ToString();
                }
                else
                {
                    model.AnnualSilkwormTwo = null;
                }
                IRow row7 = sheet.GetRow(7);
                if (row7.GetCell(1) != null && row7.GetCell(1).ToString() != "")
                {
                    model.AnnualSilkwormThree = row7.GetCell(1).ToString();
                }
                else
                {
                    model.AnnualSilkwormThree = null;
                }
                if (row7.GetCell(3) != null && row7.GetCell(3).ToString() != "")
                {
                    model.AnnualSilkwormFour = row7.GetCell(3).ToString();
                }
                else
                {
                    model.AnnualSilkwormFour = null;
                }
                IRow row9 = sheet.GetRow(9);
                if (row9.GetCell(1) != null && row9.GetCell(1).ToString() != "")
                {
                    model.TecPersonTotalNum = row9.GetCell(1).ToString();
                }
                else
                {
                    model.TecPersonTotalNum = null;
                }
                if (row9.GetCell(3) != null && row9.GetCell(3).ToString() != "")
                {
                    model.TecPersonAllInNum = row9.GetCell(3).ToString();
                }
                else
                {
                    model.TecPersonAllInNum = null;
                }
                IRow row10 = sheet.GetRow(10);
                if (row10.GetCell(1) != null && row10.GetCell(1).ToString() != "")
                {
                    model.ManNum = row10.GetCell(1).ToString();
                }
                else
                {
                    model.ManNum = null;
                }
                if (row10.GetCell(3) != null && row10.GetCell(3).ToString() != "")
                {
                    model.WomanNum = row10.GetCell(3).ToString();
                }
                else
                {
                    model.WomanNum = null;
                }
                IRow row11 = sheet.GetRow(11);
                if (row11.GetCell(1) != null && row11.GetCell(1).ToString() != "")
                {
                    model.AgeOne = row11.GetCell(1).ToString();
                }
                else
                {
                    model.AgeOne = null;
                }
                if (row11.GetCell(3) != null && row11.GetCell(3).ToString() != "")
                {
                    model.AgeTwo = row11.GetCell(3).ToString();
                }
                else
                {
                    model.AgeTwo = null;
                }
                IRow row12 = sheet.GetRow(12);
                if (row12.GetCell(1) != null && row12.GetCell(1).ToString() != "")
                {
                    model.AgeThree = row12.GetCell(1).ToString();
                }
                else
                {
                    model.AgeThree = null;
                }
                if (row12.GetCell(3) != null && row12.GetCell(3).ToString() != "")
                {
                    model.AgeFour = row12.GetCell(3).ToString();
                }
                else
                {
                    model.AgeFour = null;
                }
                IRow row13 = sheet.GetRow(13);
                if (row13.GetCell(1) != null && row13.GetCell(1).ToString() != "")
                {
                    model.EduLevelOne = row13.GetCell(1).ToString();
                }
                else
                {
                    model.EduLevelOne = null;
                }
                if (row13.GetCell(3) != null && row13.GetCell(3).ToString() != "")
                {
                    model.EduLeveTwo = row13.GetCell(3).ToString();
                }
                else
                {
                    model.EduLeveTwo = null;
                }
                IRow row14 = sheet.GetRow(14);
                if (row14.GetCell(1) != null && row14.GetCell(1).ToString() != "")
                {
                    model.EduLevelThree = row14.GetCell(1).ToString();
                }
                else
                {
                    model.EduLevelThree = null;
                }
                if (row14.GetCell(3) != null && row14.GetCell(3).ToString() != "")
                {
                    model.EduLevelFour = row14.GetCell(3).ToString();
                }
                else
                {
                    model.EduLevelFour = null;
                }
                IRow row15 = sheet.GetRow(15);
                if (row15.GetCell(1) != null && row15.GetCell(1).ToString() != "")
                {
                    model.TitleOne = row15.GetCell(1).ToString();
                }
                else
                {
                    model.TitleOne = null;
                }
                if (row15.GetCell(3) != null && row15.GetCell(3).ToString() != "")
                {
                    model.TitleTwo = row15.GetCell(3).ToString();
                }
                else
                {
                    model.TitleTwo = null;
                }
                IRow row16 = sheet.GetRow(16);
                if (row16.GetCell(1) != null && row16.GetCell(1).ToString() != "")
                {
                    model.TitleThree = row16.GetCell(1).ToString();
                }
                else
                {
                    model.TitleThree = null;
                }
                if (row16.GetCell(3) != null && row16.GetCell(3).ToString() != "")
                {
                    model.TitleFour = row16.GetCell(3).ToString();
                }
                else
                {
                    model.TitleFour = null;
                }
                IRow row18 = sheet.GetRow(18);
                if (row18.GetCell(1) != null && row18.GetCell(1).ToString() != "")
                {
                    model.IncuRoomNum = row18.GetCell(1).ToString();
                }
                else
                {
                    model.IncuRoomNum = null;
                }
                if (row18.GetCell(3) != null && row18.GetCell(3).ToString() != "")
                {
                    model.IncuRoomArea = decimal.Parse(row18.GetCell(3).ToString());
                }
                else
                {
                    model.IncuRoomArea = null;
                }
                IRow row19 = sheet.GetRow(19);
                if (row19.GetCell(1) != null && row19.GetCell(1).ToString() != "")
                {
                    model.JiCuiqingPower = decimal.Parse(row19.GetCell(1).ToString());
                }
                else
                {
                    model.JiCuiqingPower = null;
                }
                if (row19.GetCell(3) != null && row19.GetCell(3).ToString() != "")
                {
                    model.YearsOfIncu = decimal.Parse(row19.GetCell(3).ToString());
                }
                else
                {
                    model.YearsOfIncu = null;
                }
                IRow row20 = sheet.GetRow(20);
                if (row20.GetCell(1) != null && row20.GetCell(1).ToString() != "")
                {
                    model.SilkwormSpots = decimal.Parse(row20.GetCell(1).ToString());
                }
                else
                {
                    model.SilkwormSpots = null;
                }
                if (row20.GetCell(3) != null && row20.GetCell(3).ToString() != "")
                {
                    model.SilkwormArea = decimal.Parse(row20.GetCell(3).ToString());
                }
                else
                {
                    model.SilkwormArea = null;
                }
                IRow row21 = sheet.GetRow(21);
                if (row21.GetCell(1) != null && row21.GetCell(1).ToString() != "")
                {
                    model.QuaternaryFertility = decimal.Parse(row21.GetCell(1).ToString());
                }
                else
                {
                    model.QuaternaryFertility = null;
                }
                if (row21.GetCell(3) != null && row21.GetCell(3).ToString() != "")
                {
                    model.AnnualTotalFertility = decimal.Parse(row21.GetCell(3).ToString());
                }
                else
                {
                    model.AnnualTotalFertility = null;
                }
                IRow row22 = sheet.GetRow(22);
                if (row22.GetCell(1) != null && row22.GetCell(1).ToString() != "")
                {
                    model.LargeCocoonNum = decimal.Parse(row22.GetCell(1).ToString());
                }
                else
                {
                    model.LargeCocoonNum = null;
                }
                if (row22.GetCell(3) != null && row22.GetCell(3).ToString() != "")
                {
                    model.LargeCocoonArea = decimal.Parse(row22.GetCell(3).ToString());
                }
                else
                {
                    model.LargeCocoonArea = null;
                }
                IRow row23 = sheet.GetRow(23);
                if (row23.GetCell(1) != null && row23.GetCell(1).ToString() != "")
                {
                    model.LaborSavingRearTable = decimal.Parse(row23.GetCell(1).ToString());
                }
                else
                {
                    model.LaborSavingRearTable = null;
                }
                if (row23.GetCell(3) != null && row23.GetCell(3).ToString() != "")
                {
                    model.GridCluster = decimal.Parse(row23.GetCell(3).ToString());
                }
                else
                {
                    model.GridCluster = null;
                }
                IRow row24 = sheet.GetRow(24);
                if (row24.GetCell(1) != null && row24.GetCell(1).ToString() != "")
                {
                    model.CocoonStation = decimal.Parse(row24.GetCell(1).ToString());
                }
                else
                {
                    model.CocoonStation = null;
                }
                if (row24.GetCell(3) != null && row24.GetCell(3).ToString() != "")
                {
                    model.CocoonStationArea = decimal.Parse(row24.GetCell(3).ToString());
                }
                else
                {
                    model.CocoonStationArea = null;
                }
                IRow row25 = sheet.GetRow(25);
                if (row25.GetCell(1) != null && row25.GetCell(1).ToString() != "")
                {
                    model.SeasonalDryCapacity = decimal.Parse(row25.GetCell(1).ToString());
                }
                else
                {
                    model.SeasonalDryCapacity = null;
                }
                if (row25.GetCell(3) != null && row25.GetCell(3).ToString() != "")
                {
                    model.CircularDryMachine = decimal.Parse(row25.GetCell(3).ToString());
                }
                else
                {
                    model.CircularDryMachine = null;
                }
                //IRow row27 = sheet.GetRow(27);
                //if (row27.GetCell(1) != null && row27.GetCell(1).ToString() != "")
                //{
                //    model.ReelingEnterprise = decimal.Parse(row27.GetCell(1).ToString());
                //}
                //else
                //{
                //    model.ReelingEnterprise = null;
                //}
                //IRow row28 = sheet.GetRow(28);
                //if (row28.GetCell(1) != null && row28.GetCell(1).ToString() != "")
                //{
                //    model.ReelingCapacityOne = decimal.Parse(row28.GetCell(1).ToString());
                //}
                //else
                //{
                //    model.ReelingCapacityOne = null;
                //}
                //if (row28.GetCell(3) != null && row28.GetCell(3).ToString() != "")
                //{
                //    model.ReelingCapacityTwo = decimal.Parse(row28.GetCell(3).ToString());
                //}
                //else
                //{
                //    model.ReelingCapacityTwo = null;
                //}
                //IRow row29 = sheet.GetRow(29);
                //if (row29.GetCell(1) != null && row29.GetCell(1).ToString() != "")
                //{
                //    model.ReelingCapacityThree = decimal.Parse(row29.GetCell(1).ToString());
                //}
                //else
                //{
                //    model.ReelingCapacityThree = null;
                //}
                //if (row29.GetCell(3) != null && row29.GetCell(3).ToString() != "")
                //{
                //    model.ReelingCapacityFour = decimal.Parse(row29.GetCell(3).ToString());
                //}
                //else
                //{
                //    model.ReelingCapacityFour = null;
                //}
                //IRow row30 = sheet.GetRow(30);
                //if (row30.GetCell(1) != null && row30.GetCell(1).ToString() != "")
                //{
                //    model.SilkwormField = decimal.Parse(row30.GetCell(1).ToString());
                //}
                //else
                //{
                //    model.SilkwormField = null;
                //}
                //if (row30.GetCell(3) != null && row30.GetCell(3).ToString() != "")
                //{
                //    model.ThroughPut = decimal.Parse(row30.GetCell(3).ToString());
                //}
                //else
                //{
                //    model.ThroughPut = null;
                //}
                //IRow row31 = sheet.GetRow(31);
                //if (row31.GetCell(1) != null && row31.GetCell(1).ToString() != "")
                //{
                //    model.SilkEnterprise = decimal.Parse(row31.GetCell(1).ToString());
                //}
                //else
                //{
                //    model.SilkEnterprise = null;
                //}
                //if (row31.GetCell(3) != null && row31.GetCell(3).ToString() != "")
                //{
                //    model.ProcessedSilkQuilt = decimal.Parse(row31.GetCell(3).ToString());
                //}
                //else
                //{
                //    model.ProcessedSilkQuilt = null;
                //}
                //IRow row32 = sheet.GetRow(32);
                //if (row32.GetCell(1) != null && row32.GetCell(1).ToString() != "")
                //{
                //    model.Other = row32.GetCell(1).ToString();
                //}
                //else
                //{
                //    model.Other = null;
                //}
                //if (row32.GetCell(3) != null && row32.GetCell(3).ToString() != "")
                //{
                //    model.OtherThroughPut = decimal.Parse(row32.GetCell(3).ToString());
                //}
                //else
                //{
                //    model.OtherThroughPut = null;
                //}

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// 添加
        /// </summary>
        public virtual ResResult<int> AddInfo(MulProdInfoModel model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_MulProdInfoChart";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "NumTownship,";
            strSql += "NumTownshipName,";
            strSql += "NumVillage,";
            strSql += "NumVillageName,";
            strSql += "MulHouseholds,";
            strSql += "AnnualSilkwormOne,";
            strSql += "AnnualSilkwormTwo,";
            strSql += "AnnualSilkwormThree,";
            strSql += "AnnualSilkwormFour,";
            strSql += "TecPersonTotalNum,";
            strSql += "TecPersonAllInNum,";
            strSql += "ManNum,";
            strSql += "WomanNum,";
            strSql += "AgeOne,";
            strSql += "AgeTwo,";
            strSql += "AgeThree,";
            strSql += "AgeFour,";
            strSql += "EduLevelOne,";
            strSql += "EduLeveTwo,";
            strSql += "EduLevelThree,";
            strSql += "EduLevelFour,";
            strSql += "TitleOne,";
            strSql += "TitleTwo,";
            strSql += "TitleThree,";
            strSql += "TitleFour,";
            strSql += "IncuRoomNum,";
            strSql += "IncuRoomArea,";
            strSql += "JiCuiqingPower,";
            strSql += "YearsOfIncu,";
            strSql += "SilkwormSpots,";
            strSql += "SilkwormArea,";
            strSql += "QuaternaryFertility,";
            strSql += "AnnualTotalFertility,";
            strSql += "LargeCocoonNum,";
            strSql += "LargeCocoonArea,";
            strSql += "LaborSavingRearTable,";
            strSql += "GridCluster,";
            strSql += "CocoonStation,";
            strSql += "CocoonStationArea,";
            strSql += "SeasonalDryCapacity,";
            strSql += "CircularDryMachine)";
            //strSql += "ReelingEnterprise,";
            //strSql += "ReelingCapacityOne,";
            //strSql += "ReelingCapacityTwo,";
            //strSql += "ReelingCapacityThree,";
            //strSql += "ReelingCapacityFour,";
            //strSql += "SilkwormField,";
            //strSql += "ThroughPut,";
            //strSql += "SilkEnterprise,";
            //strSql += "ProcessedSilkQuilt,";
            //strSql += "Other,";
            //strSql += "OtherThroughPut)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@NumTownship,";
            strSql += "@NumTownshipName,";
            strSql += "@NumVillage,";
            strSql += "@NumVillageName,";
            strSql += "@MulHouseholds,";
            strSql += "@AnnualSilkwormOne,";
            strSql += "@AnnualSilkwormTwo,";
            strSql += "@AnnualSilkwormThree,";
            strSql += "@AnnualSilkwormFour,";
            strSql += "@TecPersonTotalNum,";
            strSql += "@TecPersonAllInNum,";
            strSql += "@ManNum,";
            strSql += "@WomanNum,";
            strSql += "@AgeOne,";
            strSql += "@AgeTwo,";
            strSql += "@AgeThree,";
            strSql += "@AgeFour,";
            strSql += "@EduLevelOne,";
            strSql += "@EduLeveTwo,";
            strSql += "@EduLevelThree,";
            strSql += "@EduLevelFour,";
            strSql += "@TitleOne,";
            strSql += "@TitleTwo,";
            strSql += "@TitleThree,";
            strSql += "@TitleFour,";
            strSql += "@IncuRoomNum,";
            strSql += "@IncuRoomArea,";
            strSql += "@JiCuiqingPower,";
            strSql += "@YearsOfIncu,";
            strSql += "@SilkwormSpots,";
            strSql += "@SilkwormArea,";
            strSql += "@QuaternaryFertility,";
            strSql += "@AnnualTotalFertility,";
            strSql += "@LargeCocoonNum,";
            strSql += "@LargeCocoonArea,";
            strSql += "@LaborSavingRearTable,";
            strSql += "@GridCluster,";
            strSql += "@CocoonStation,";
            strSql += "@CocoonStationArea,";
            strSql += "@SeasonalDryCapacity,";
            strSql += "@CircularDryMachine)";
            //strSql += "@ReelingEnterprise,";
            //strSql += "@ReelingCapacityOne,";
            //strSql += "@ReelingCapacityTwo,";
            //strSql += "@ReelingCapacityThree,";
            //strSql += "@ReelingCapacityFour,";
            //strSql += "@SilkwormField,";
            //strSql += "@ThroughPut,";
            //strSql += "@SilkEnterprise,";
            //strSql += "@ProcessedSilkQuilt,";
            //strSql += "@Other,";
            //strSql += "@OtherThroughPut)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id = CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增表10县生产信息调查统计表T_MulProdInfoChart对象信息失败", ex);
                return obj;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        public ResResult<int> DeleteData(string id)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"delete from dbo.T_MulProdInfoChart where ReportId = @Id";
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, new { Id = id });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("删除表10县生产信息调查统计表T_MulProdInfoChart对象信息失败", ex);
                return obj;
            }
        }


        /// <summary>
        /// 查询万亩以上乡(镇)数名称和千亩以上村(委会)名称
        /// </summary>
        /// <param name="list"></param>
        /// <param name="FillInYear"></param>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public List<MulProdInfoStatsModel> GetNumName(List<MulProdInfoStatsModel> list,string FillInYear,int start,int length) {
            string StrAreaSql = "select AreaCode,NumTownshipName,NumVillageName from T_Report r left join T_MulProdInfoChart mp on mp.ReportId = r.Id ";
            StrAreaSql += "where ReportCode = 'MulProdInfoChart' and FillInYear = @FillInYear and AreaCode like @AreaCode";
            string NumTownshipName = "";
            string NumVillageName = "";

            foreach (var item in list) {
                var dbCon = ConnectFactory.GetOpenConnection();
                var paramlist = new DynamicParameters();
                paramlist.Add("FillInYear", FillInYear);
                string text = "%" + item.AreaCode.Substring(start, length) + "%";
                paramlist.Add("AreaCode", text);
                List<MulProdInfoStatsModel> NameList = new List<MulProdInfoStatsModel>();
                NameList = dbCon.Query<MulProdInfoStatsModel>(StrAreaSql, paramlist).ToList();
                foreach (var s in NameList) {
                    if (s.NumTownshipName == null) {
                        s.NumTownshipName = "";
                    }else
                    {
                        NumTownshipName = NumTownshipName + s.NumTownshipName + ',';
                    }
                    if (s.NumVillageName == null) {
                        s.NumVillageName = "";
                    }else
                    {
                        NumVillageName = NumVillageName + s.NumVillageName + ',';
                    }
                }
                item.NumTownshipName = NumTownshipName;
                item.NumVillageName = NumVillageName;
                dbCon.Dispose();//数据库连接释放
            }
            return list;
        }

        /// <summary>
        /// 桑园面积统计--------(按年统计)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<MulProdInfoStatsModel>> GetStatsList(MulNowModel model)
        {
            DictServices dictServices = new DictServices();
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetPZKey(model.FillInYear, "ReportYear");
            }

            string ProvinceCode = "530000000000";
            ResResult<List<MulProdInfoStatsModel>> result = new ResResult<List<MulProdInfoStatsModel>>();
            /*县级统计代码*/
            string StrAreaSql = "select Area.Code AreaCode, Dict.DictText year, Area.FullName AreaName,Coord";
            StrAreaSql += ",ISNULL(NumTownship,0.00) NumTownship";
            StrAreaSql += ",ISNULL(NumVillage,0.00) NumVillage";
            StrAreaSql += ",ISNULL(MulHouseholds,0.00)MulHouseholds";
            StrAreaSql += ",ISNULL(AnnualSilkwormOne,0.00) AnnualSilkwormOne";
            StrAreaSql += ",ISNULL(AnnualSilkwormTwo,0.00)AnnualSilkwormTwo";
            StrAreaSql += ",ISNULL(AnnualSilkwormThree,0.00) AnnualSilkwormThree";
            StrAreaSql += ",ISNULL(AnnualSilkwormFour,0.00) AnnualSilkwormFour";
            StrAreaSql += ",ISNULL(TecPersonTotalNum,0.00) TecPersonTotalNum";
            StrAreaSql += ",ISNULL(TecPersonAllInNum,0.00) TecPersonAllInNum";
            StrAreaSql += ",ISNULL(ManNum,0.00) ManNum";
            StrAreaSql += ",ISNULL(WomanNum,0.00) WomanNum";
            StrAreaSql += ",ISNULL(AgeOne,0.00) AgeOne";
            StrAreaSql += ",ISNULL(AgeTwo,0.00) AgeTwo";
            StrAreaSql += ",ISNULL(AgeThree,0.00) AgeThree";
            StrAreaSql += ",ISNULL(AgeFour,0.00) AgeFour";
            StrAreaSql += ",ISNULL(EduLevelOne,0.00) EduLevelOne ";
            StrAreaSql += ",ISNULL(EduLeveTwo,0.00)  EduLeveTwo";
            StrAreaSql += ",ISNULL(EduLevelThree,0.00) EduLevelThree";
            StrAreaSql += ",ISNULL(EduLevelFour,0.00) EduLevelFour";
            StrAreaSql += ",ISNULL(TitleOne,0.00) TitleOne";
            StrAreaSql += ",ISNULL(TitleTwo,0.00) TitleTwo";
            StrAreaSql += ",ISNULL(TitleThree,0.00) TitleThree";
            StrAreaSql += ",ISNULL(TitleFour,0.00) TitleFour";
            StrAreaSql += ",ISNULL(IncuRoomNum,0.00) IncuRoomNum";
            StrAreaSql += ",ISNULL(IncuRoomArea,0.00) IncuRoomArea";
            StrAreaSql += ",ISNULL(JiCuiqingPower,0.00) JiCuiqingPower";
            StrAreaSql += ",ISNULL(YearsOfIncu,0.00) YearsOfIncu";
            StrAreaSql += ",ISNULL(SilkwormSpots,0.00) SilkwormSpots";
            StrAreaSql += ",ISNULL(SilkwormArea,0.00) SilkwormArea";
            StrAreaSql += ",ISNULL(QuaternaryFertility,0.00) QuaternaryFertility";
            StrAreaSql += ",ISNULL(AnnualTotalFertility,0.00) AnnualTotalFertility";
            StrAreaSql += ",ISNULL(LargeCocoonNum,0.00) LargeCocoonNum";
            StrAreaSql += ",ISNULL(LargeCocoonArea,0.00) LargeCocoonArea";
            StrAreaSql += ",ISNULL(LaborSavingRearTable,0.00) LaborSavingRearTable";
            StrAreaSql += ",ISNULL(GridCluster,0.00) GridCluster";
            StrAreaSql += ",ISNULL(CocoonStation,0.00) CocoonStation";
            StrAreaSql += ",ISNULL(CocoonStationArea,0.00) CocoonStationArea";
            StrAreaSql += ",ISNULL(SeasonalDryCapacity,0.00) SeasonalDryCapacity";
            StrAreaSql += ",ISNULL(CircularDryMachine,0.00)  CircularDryMachine  ";
            StrAreaSql += " from(";
            StrAreaSql += "select ";
            StrAreaSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code ";
            StrAreaSql += ",SUM(ISNULL(NumTownship,0.00)) NumTownship";
            StrAreaSql += ",SUM(ISNULL(NumVillage,0.00)) NumVillage";
            StrAreaSql += ",SUM(ISNULL(MulHouseholds,0.00))MulHouseholds";
            StrAreaSql += ",SUM(ISNULL(AnnualSilkwormOne,0.00)) AnnualSilkwormOne";
            StrAreaSql += ",SUM(ISNULL(AnnualSilkwormTwo,0.00))AnnualSilkwormTwo";
            StrAreaSql += ",SUM(ISNULL(AnnualSilkwormThree,0.00)) AnnualSilkwormThree";
            StrAreaSql += ",SUM(ISNULL(AnnualSilkwormFour,0.00)) AnnualSilkwormFour";
            StrAreaSql += ",SUM(ISNULL(TecPersonTotalNum,0.00)) TecPersonTotalNum";
            StrAreaSql += ",SUM(ISNULL(TecPersonAllInNum,0.00)) TecPersonAllInNum";
            StrAreaSql += ",SUM(ISNULL(ManNum,0.00)) ManNum";
            StrAreaSql += ",SUM(ISNULL(WomanNum,0.00)) WomanNum";
            StrAreaSql += ",SUM(ISNULL(AgeOne,0.00)) AgeOne";
            StrAreaSql += ",SUM(ISNULL(AgeTwo,0.00)) AgeTwo";
            StrAreaSql += ",SUM(ISNULL(AgeThree,0.00)) AgeThree";
            StrAreaSql += ",SUM(ISNULL(AgeFour,0.00)) AgeFour";
            StrAreaSql += ",SUM(ISNULL(EduLevelOne,0.00)) EduLevelOne ";
            StrAreaSql += ",SUM(ISNULL(EduLeveTwo,0.00))  EduLeveTwo";
            StrAreaSql += ",SUM(ISNULL(EduLevelThree,0.00)) EduLevelThree";
            StrAreaSql += ",SUM(ISNULL(EduLevelFour,0.00)) EduLevelFour";
            StrAreaSql += ",SUM(ISNULL(TitleOne,0.00)) TitleOne";
            StrAreaSql += ",SUM(ISNULL(TitleTwo,0.00)) TitleTwo";
            StrAreaSql += ",SUM(ISNULL(TitleThree,0.00)) TitleThree";
            StrAreaSql += ",SUM(ISNULL(TitleFour,0.00)) TitleFour";
            StrAreaSql += ",SUM(ISNULL(IncuRoomNum,0.00)) IncuRoomNum";
            StrAreaSql += ",SUM(ISNULL(IncuRoomArea,0.00)) IncuRoomArea";
            StrAreaSql += ",SUM(ISNULL(JiCuiqingPower,0.00)) JiCuiqingPower";
            StrAreaSql += ",SUM(ISNULL(YearsOfIncu,0.00)) YearsOfIncu";
            StrAreaSql += ",SUM(ISNULL(SilkwormSpots,0.00)) SilkwormSpots";
            StrAreaSql += ",SUM(ISNULL(SilkwormArea,0.00)) SilkwormArea";
            StrAreaSql += ",SUM(ISNULL(QuaternaryFertility,0.00)) QuaternaryFertility";
            StrAreaSql += ",SUM(ISNULL(AnnualTotalFertility,0.00)) AnnualTotalFertility";
            StrAreaSql += ",SUM(ISNULL(LargeCocoonNum,0.00)) LargeCocoonNum";
            StrAreaSql += ",SUM(ISNULL(LargeCocoonArea,0.00)) LargeCocoonArea";
            StrAreaSql += ",SUM(ISNULL(LaborSavingRearTable,0.00)) LaborSavingRearTable";
            StrAreaSql += ",SUM(ISNULL(GridCluster,0.00)) GridCluster";
            StrAreaSql += ",SUM(ISNULL(CocoonStation,0.00)) CocoonStation";
            StrAreaSql += ",SUM(ISNULL(CocoonStationArea,0.00)) CocoonStationArea";
            StrAreaSql += ",SUM(ISNULL(SeasonalDryCapacity,0.00)) SeasonalDryCapacity";
            StrAreaSql += ",SUM(ISNULL(CircularDryMachine,0.00))  CircularDryMachine    ";
            StrAreaSql += "from([dbo].[T_Report] Report left join T_MulProdInfoChart MProdInfo  on MProdInfo.ReportId = Report.Id  ) ";
            StrAreaSql += "right join T_Area Area on Area.Code = Report.AreaCode ";
            StrAreaSql += "where ";
            StrAreaSql += "Area.ParentCode = @AreaCode or Area.Code= @AreaCode and Report.Status=@ReportStatus and Report.FillInYear=@FillInYear  ";
            StrAreaSql += "group by rollup(Area.Code)) tt1 left join T_Area Area on tt1.Code = Area.Code inner join T_Dict Dict  on Dict.GroupCode='ReportYear' and DictValue=@FillInYear ";
            StrAreaSql += "where area.ParentCode=@AreaCode  or area.Code=@AreaCode ";
            StrAreaSql += "order by AreaCode asc ";

            /*市级统计代码*/
            string StrCitySql = "select Area.Code AreaCode,Dict.DictText year, Area.FullName AreaName, Coord ";
            StrCitySql += ",ISNULL(NumTownship,0.00) NumTownship";
            StrCitySql += ",ISNULL(NumVillage,0.00) NumVillage";
            StrCitySql += ",ISNULL(MulHouseholds,0.00)MulHouseholds";
            StrCitySql += ",ISNULL(AnnualSilkwormOne,0.00) AnnualSilkwormOne";
            StrCitySql += ",ISNULL(AnnualSilkwormTwo,0.00)AnnualSilkwormTwo";
            StrCitySql += ",ISNULL(AnnualSilkwormThree,0.00) AnnualSilkwormThree";
            StrCitySql += ",ISNULL(AnnualSilkwormFour,0.00) AnnualSilkwormFour";
            StrCitySql += ",ISNULL(TecPersonTotalNum,0.00) TecPersonTotalNum";
            StrCitySql += ",ISNULL(TecPersonAllInNum,0.00) TecPersonAllInNum";
            StrCitySql += ",ISNULL(ManNum,0.00) ManNum";
            StrCitySql += ",ISNULL(WomanNum,0.00) WomanNum";
            StrCitySql += ",ISNULL(AgeOne,0.00) AgeOne";
            StrCitySql += ",ISNULL(AgeTwo,0.00) AgeTwo";
            StrCitySql += ",ISNULL(AgeThree,0.00) AgeThree";
            StrCitySql += ",ISNULL(AgeFour,0.00) AgeFour";
            StrCitySql += ",ISNULL(EduLevelOne,0.00) EduLevelOne ";
            StrCitySql += ",ISNULL(EduLeveTwo,0.00)  EduLeveTwo";
            StrCitySql += ",ISNULL(EduLevelThree,0.00) EduLevelThree";
            StrCitySql += ",ISNULL(EduLevelFour,0.00) EduLevelFour";
            StrCitySql += ",ISNULL(TitleOne,0.00) TitleOne";
            StrCitySql += ",ISNULL(TitleTwo,0.00) TitleTwo";
            StrCitySql += ",ISNULL(TitleThree,0.00) TitleThree";
            StrCitySql += ",ISNULL(TitleFour,0.00) TitleFour";
            StrCitySql += ",ISNULL(IncuRoomNum,0.00) IncuRoomNum";
            StrCitySql += ",ISNULL(IncuRoomArea,0.00) IncuRoomArea";
            StrCitySql += ",ISNULL(JiCuiqingPower,0.00) JiCuiqingPower";
            StrCitySql += ",ISNULL(YearsOfIncu,0.00) YearsOfIncu";
            StrCitySql += ",ISNULL(SilkwormSpots,0.00) SilkwormSpots";
            StrCitySql += ",ISNULL(SilkwormArea,0.00) SilkwormArea";
            StrCitySql += ",ISNULL(QuaternaryFertility,0.00) QuaternaryFertility";
            StrCitySql += ",ISNULL(AnnualTotalFertility,0.00) AnnualTotalFertility";
            StrCitySql += ",ISNULL(LargeCocoonNum,0.00) LargeCocoonNum";
            StrCitySql += ",ISNULL(LargeCocoonArea,0.00) LargeCocoonArea";
            StrCitySql += ",ISNULL(LaborSavingRearTable,0.00) LaborSavingRearTable";
            StrCitySql += ",ISNULL(GridCluster,0.00) GridCluster";
            StrCitySql += ",ISNULL(CocoonStation,0.00) CocoonStation";
            StrCitySql += ",ISNULL(CocoonStationArea,0.00) CocoonStationArea";
            StrCitySql += ",ISNULL(SeasonalDryCapacity,0.00) SeasonalDryCapacity";
            StrCitySql += ",ISNULL(CircularDryMachine,0.00)  CircularDryMachine  ";
            StrCitySql += "from( ";
            StrCitySql += "select ";
            StrCitySql += "case when grouping(Area.ParentCode) = 1 then @AreaCode else ParentCode end ParentCode ";
            StrCitySql += ",SUM(ISNULL(NumTownship,0.00)) NumTownship";
            StrCitySql += ",SUM(ISNULL(NumVillage,0.00)) NumVillage";
            StrCitySql += ",SUM(ISNULL(MulHouseholds,0.00))MulHouseholds";
            StrCitySql += ",SUM(ISNULL(AnnualSilkwormOne,0.00)) AnnualSilkwormOne";
            StrCitySql += ",SUM(ISNULL(AnnualSilkwormTwo,0.00))AnnualSilkwormTwo";
            StrCitySql += ",SUM(ISNULL(AnnualSilkwormThree,0.00)) AnnualSilkwormThree";
            StrCitySql += ",SUM(ISNULL(AnnualSilkwormFour,0.00)) AnnualSilkwormFour";
            StrCitySql += ",SUM(ISNULL(TecPersonTotalNum,0.00)) TecPersonTotalNum";
            StrCitySql += ",SUM(ISNULL(TecPersonAllInNum,0.00)) TecPersonAllInNum";
            StrCitySql += ",SUM(ISNULL(ManNum,0.00)) ManNum";
            StrCitySql += ",SUM(ISNULL(WomanNum,0.00)) WomanNum";
            StrCitySql += ",SUM(ISNULL(AgeOne,0.00)) AgeOne";
            StrCitySql += ",SUM(ISNULL(AgeTwo,0.00)) AgeTwo";
            StrCitySql += ",SUM(ISNULL(AgeThree,0.00)) AgeThree";
            StrCitySql += ",SUM(ISNULL(AgeFour,0.00)) AgeFour";
            StrCitySql += ",SUM(ISNULL(EduLevelOne,0.00)) EduLevelOne ";
            StrCitySql += ",SUM(ISNULL(EduLeveTwo,0.00))  EduLeveTwo";
            StrCitySql += ",SUM(ISNULL(EduLevelThree,0.00)) EduLevelThree";
            StrCitySql += ",SUM(ISNULL(EduLevelFour,0.00)) EduLevelFour";
            StrCitySql += ",SUM(ISNULL(TitleOne,0.00)) TitleOne";
            StrCitySql += ",SUM(ISNULL(TitleTwo,0.00)) TitleTwo";
            StrCitySql += ",SUM(ISNULL(TitleThree,0.00)) TitleThree";
            StrCitySql += ",SUM(ISNULL(TitleFour,0.00)) TitleFour";
            StrCitySql += ",SUM(ISNULL(IncuRoomNum,0.00)) IncuRoomNum";
            StrCitySql += ",SUM(ISNULL(IncuRoomArea,0.00)) IncuRoomArea";
            StrCitySql += ",SUM(ISNULL(JiCuiqingPower,0.00)) JiCuiqingPower";
            StrCitySql += ",SUM(ISNULL(YearsOfIncu,0.00)) YearsOfIncu";
            StrCitySql += ",SUM(ISNULL(SilkwormSpots,0.00)) SilkwormSpots";
            StrCitySql += ",SUM(ISNULL(SilkwormArea,0.00)) SilkwormArea";
            StrCitySql += ",SUM(ISNULL(QuaternaryFertility,0.00)) QuaternaryFertility";
            StrCitySql += ",SUM(ISNULL(AnnualTotalFertility,0.00)) AnnualTotalFertility";
            StrCitySql += ",SUM(ISNULL(LargeCocoonNum,0.00)) LargeCocoonNum";
            StrCitySql += ",SUM(ISNULL(LargeCocoonArea,0.00)) LargeCocoonArea";
            StrCitySql += ",SUM(ISNULL(LaborSavingRearTable,0.00)) LaborSavingRearTable";
            StrCitySql += ",SUM(ISNULL(GridCluster,0.00)) GridCluster";
            StrCitySql += ",SUM(ISNULL(CocoonStation,0.00)) CocoonStation";
            StrCitySql += ",SUM(ISNULL(CocoonStationArea,0.00)) CocoonStationArea";
            StrCitySql += ",SUM(ISNULL(SeasonalDryCapacity,0.00)) SeasonalDryCapacity";
            StrCitySql += ",SUM(ISNULL(CircularDryMachine,0.00))  CircularDryMachine    ";
            StrCitySql += "from t_report t1  ";
            StrCitySql += "left  join  T_MulProdInfoChart MProdInfo  on MProdInfo.ReportId = t1.Id  ";
            StrCitySql += "right  join T_Area Area on Area.Code = t1.AreaCode  ";
            StrCitySql += "where ParentCode!=@AreaCode  and t1.FillInYear=@FillInYear and t1.Status=@ReportStatus ";
            StrCitySql += "group by rollup(Area.ParentCode)  ";
            StrCitySql += ") t left join T_Area  area on area.Code = t.ParentCode  ";
            StrCitySql += "inner join T_Dict Dict  on Dict.GroupCode = 'ReportYear' and DictValue = @FillInYear  ";
            StrCitySql += "where area.ParentCode=@AreaCode  or area.Code=@AreaCode ";
            StrCitySql += "order by AreaCode asc ";

            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var paramlist = new DynamicParameters();
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("FillInYear", model.FillInYear);
                    paramlist.Add("ReportStatus", model.Status);
                    List<MulProdInfoStatsModel> list = new List<MulProdInfoStatsModel>();
                    if (model.AreaCode != ProvinceCode)//不为省级用户
                    {
                        list = dbCon.Query<MulProdInfoStatsModel>(StrAreaSql, paramlist).ToList();
                        result.model = list;
                    }
                    else
                    {
                        list = dbCon.Query<MulProdInfoStatsModel>(StrCitySql, paramlist).ToList();
                        //list = GetNumName(list, model.FillInYear,0,4);
                        //List<MulProdInfoStatsModel> list2 = new List<MulProdInfoStatsModel>();
                        //list.Where(s => s.AreaCode == model.AreaCode).FirstOrDefault().AreaName = "合计";
                        //MulProdInfoStatsModel m = list.Where(s => s.AreaName == "合计").FirstOrDefault();
                        //list.Remove(m);
                        //list2.Add(m);
                        //for (var i = 0; i < list.Count; i++)
                        //{
                        //    list2.Add(list[i]);
                        //}
                        result.model = list;
                    }
                    
                    if (list != null)
                    {
                        result.status = DefaultKey.SuccessResult;
                        result.message = "获取县生产信息统计数据成功！";
                    }
                    else
                    {
                        result.status = DefaultKey.InfoResult;
                        result.message = "获取县生产信息统计数据失败！";
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取县生产信息统计数据失败", ex);
            }
            return result;
        }

        /// <summary>
        /// 获取时间趋势图数据列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<MulProdInfoStatsModel>> GetStatsByTimeList(MulNowModel model)
        {
            DictServices dictServices = new DictServices();
            List<Select> listDict = dictServices.GetListByGroupCode("ReportYear").model;
            ResResult<List<MulProdInfoStatsModel>> result = new ResResult<List<MulProdInfoStatsModel>>();
            List<MulProdInfoStatsModel> list = new List<MulProdInfoStatsModel>();
            if (listDict.Count > 0)
            {
                if (!string.IsNullOrEmpty(model.AreaCode))
                {
                    for (var i = 0; i < listDict.Count; i++)
                    {
                        model.FillInYear = listDict[i].label;
                        List<MulProdInfoStatsModel> listOne = GetStatsList(model).model;
                        if (listOne.Count > 0)
                        {
                            list.Add(listOne[0]);
                        }
                    }
                    if (list.Count > 0)
                    {
                        result.message = "时间趋势数据列表获取成功";
                        result.model = list.OrderBy(s => s.year).ToList();
                        result.status = DefaultKey.SuccessResult;
                    }
                    else
                    {
                        result.message = "时间趋势数据列表为空";
                        result.status = DefaultKey.InfoResult;
                    }
                }
                else
                {
                    result.message = "时间趋势数据列表失败,当前地区不存在";
                    result.status = DefaultKey.ErrorResult;
                }
            }
            else
            {
                result.message = "时间趋势数据列表失败,时间为空";
                result.status = DefaultKey.ErrorResult;
            }
            return result;
        }
        ///获取统计数据详情
        public ResResult<List<MulProdInfoStatsModel>> GetDetailStats(MulNowModel model)
        {
            ResResult<List<MulProdInfoStatsModel>> res = new ResResult<List<MulProdInfoStatsModel>>();
            List<MulProdInfoStatsModel> list = new List<MulProdInfoStatsModel>();
            string ProvinceCode = DefaultKey.ProvinceCode;
            string FillInYear = model.FillInYear;
            try
            {
                if (model.AreaCode == ProvinceCode)
                {
                    var First = GetStatsList(model).model;
                    //First[0].AreaName = "合计";
                    list.Add(First[0]);//省级汇总合计
                    foreach (var item in First)
                    {
                        if (item.AreaCode != ProvinceCode)
                        {
                            MulNowModel model2 = new MulNowModel();
                            model2.FillInYear = FillInYear;
                            model2.AreaCode = item.AreaCode;
                            model2.Status = model.Status;
                            var Second = GetStatsList(model2).model;
                            list.Add(item);
                            foreach (var item2 in Second)
                            {
                                if (model2.AreaCode != item2.AreaCode)
                                {
                                    list.Add(item2);
                                }
                            }
                        }
                    }
                }
                else
                {
                    list = GetStatsList(model).model;//数据获取
                }
            }
            catch (Exception ex)
            {
                res.model = list;
                res.message = "获取统计数据详情失败" + ex.ToString().Trim();
                res.status = DefaultKey.SuccessResult;
            }
            if (list != null)
            {
                res.model = list;
                res.message = "获取统计数据详情成功";
                res.status = DefaultKey.SuccessResult;
            }
            else
            {
                res.message = "获取统计数据详情成失败";
                res.status = DefaultKey.ErrorResult;
            }
            return res;
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(MulNowModel model)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\Excel.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulProdInfoChart") as HSSFSheet;//读取模板所在sheet位置
            MulProdViewModel pagelist = GetModel(model).model;//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式

            HSSFRow datarow2 = (HSSFRow)sheet.GetRow(2);
            datarow2.GetCell(1).SetCellValue(pagelist.NumTownship == null ? "" : Common.GetInt(pagelist.NumTownship));
            datarow2.GetCell(3).SetCellValue(pagelist.NumTownshipName == null ? "" : pagelist.NumTownshipName.ToString());
            datarow2.GetCell(1).CellStyle = style;
            datarow2.GetCell(3).CellStyle = style;
            HSSFRow datarow3 = (HSSFRow)sheet.GetRow(3);
            datarow3.GetCell(1).SetCellValue(pagelist.NumVillage == null ? "" : Common.GetInt(pagelist.NumVillage));
            datarow3.GetCell(3).SetCellValue(pagelist.NumVillageName == null ? "" : pagelist.NumVillageName.ToString());
            datarow3.GetCell(1).CellStyle = style;
            datarow3.GetCell(3).CellStyle = style;
            HSSFRow datarow4 = (HSSFRow)sheet.GetRow(4);
            datarow4.GetCell(1).SetCellValue(pagelist.MulHouseholds == null ? "" : Common.GetInt(pagelist.MulHouseholds));
            datarow4.GetCell(1).CellStyle = style;
            HSSFRow datarow5 = (HSSFRow)sheet.GetRow(5);
            datarow5.GetCell(1).SetCellValue(pagelist.AnnualSilkwormOne == null ? "" : Common.GetInt(pagelist.AnnualSilkwormOne));
            datarow5.GetCell(3).SetCellValue(pagelist.AnnualSilkwormTwo == null ? "" : Common.GetInt(pagelist.AnnualSilkwormTwo));
            datarow5.GetCell(1).CellStyle = style;
            datarow5.GetCell(3).CellStyle = style;
            HSSFRow datarow6 = (HSSFRow)sheet.GetRow(6);
            datarow6.GetCell(1).SetCellValue(pagelist.AnnualSilkwormThree == null ? "" : Common.GetInt(pagelist.AnnualSilkwormThree));
            datarow6.GetCell(3).SetCellValue(pagelist.AnnualSilkwormFour == null ? "" : Common.GetInt(pagelist.AnnualSilkwormFour));
            datarow6.GetCell(1).CellStyle = style;
            datarow6.GetCell(3).CellStyle = style;
            HSSFRow datarow8 = (HSSFRow)sheet.GetRow(8);
            datarow8.GetCell(1).SetCellValue(pagelist.TecPersonTotalNum == null ? "" : Common.GetInt(pagelist.TecPersonTotalNum));
            datarow8.GetCell(3).SetCellValue(pagelist.TecPersonAllInNum == null ? "" : Common.GetInt(pagelist.TecPersonAllInNum));
            datarow8.GetCell(1).CellStyle = style;
            datarow8.GetCell(3).CellStyle = style;
            HSSFRow datarow9 = (HSSFRow)sheet.GetRow(9);
            datarow9.GetCell(1).SetCellValue(pagelist.ManNum == null ? "" : Common.GetInt(pagelist.ManNum));
            datarow9.GetCell(3).SetCellValue(pagelist.WomanNum == null ? "" : Common.GetInt(pagelist.WomanNum));
            datarow9.GetCell(1).CellStyle = style;
            datarow9.GetCell(3).CellStyle = style;
            HSSFRow datarow10 = (HSSFRow)sheet.GetRow(10);
            datarow10.GetCell(1).SetCellValue(pagelist.AgeOne == null ? "" : Common.GetInt(pagelist.AgeOne));
            datarow10.GetCell(3).SetCellValue(pagelist.AgeTwo == null ? "" : Common.GetInt(pagelist.AgeTwo));
            datarow10.GetCell(1).CellStyle = style;
            datarow10.GetCell(3).CellStyle = style;
            HSSFRow datarow11 = (HSSFRow)sheet.GetRow(11);
            datarow11.GetCell(1).SetCellValue(pagelist.AgeThree == null ? "" : Common.GetInt(pagelist.AgeThree));
            datarow11.GetCell(3).SetCellValue(pagelist.AgeFour == null ? "" : Common.GetInt(pagelist.AgeFour));
            datarow11.GetCell(1).CellStyle = style;
            datarow11.GetCell(3).CellStyle = style;
            HSSFRow datarow12 = (HSSFRow)sheet.GetRow(12);
            datarow12.GetCell(1).SetCellValue(pagelist.EduLevelOne == null ? "" : Common.GetInt(pagelist.EduLevelOne));
            datarow12.GetCell(3).SetCellValue(pagelist.EduLeveTwo == null ? "" : Common.GetInt(pagelist.EduLeveTwo));
            datarow12.GetCell(1).CellStyle = style;
            datarow12.GetCell(3).CellStyle = style;
            HSSFRow datarow13 = (HSSFRow)sheet.GetRow(13);
            datarow13.GetCell(1).SetCellValue(pagelist.EduLevelThree == null ? "" : Common.GetInt(pagelist.EduLevelThree));
            datarow13.GetCell(3).SetCellValue(pagelist.EduLevelFour == null ? "" : Common.GetInt(pagelist.EduLevelFour));
            datarow13.GetCell(1).CellStyle = style;
            datarow13.GetCell(3).CellStyle = style;
            HSSFRow datarow14 = (HSSFRow)sheet.GetRow(14);
            datarow14.GetCell(1).SetCellValue(pagelist.TitleOne == null ? "" : Common.GetInt(pagelist.TitleOne));
            datarow14.GetCell(3).SetCellValue(pagelist.TitleTwo == null ? "" : Common.GetInt(pagelist.TitleTwo));
            datarow14.GetCell(1).CellStyle = style;
            datarow14.GetCell(3).CellStyle = style;
            HSSFRow datarow15 = (HSSFRow)sheet.GetRow(15);
            datarow15.GetCell(1).SetCellValue(pagelist.TitleThree == null ? "" : Common.GetInt(pagelist.TitleThree));
            datarow15.GetCell(3).SetCellValue(pagelist.TitleFour == null ? "" : Common.GetInt(pagelist.TitleFour));
            datarow15.GetCell(1).CellStyle = style;
            datarow15.GetCell(3).CellStyle = style;
            HSSFRow datarow17 = (HSSFRow)sheet.GetRow(17);
            datarow17.GetCell(1).SetCellValue(pagelist.IncuRoomNum == null ? "" : Common.GetInt(pagelist.IncuRoomNum));
            datarow17.GetCell(3).SetCellValue(pagelist.IncuRoomArea == null ? "" :Common.GetOne( pagelist.IncuRoomArea));
            datarow17.GetCell(1).CellStyle = style;
            datarow17.GetCell(3).CellStyle = style;
            HSSFRow datarow18 = (HSSFRow)sheet.GetRow(18);
            datarow18.GetCell(1).SetCellValue(pagelist.JiCuiqingPower == null ? "" : Common.GetInt(pagelist.JiCuiqingPower));
            datarow18.GetCell(3).SetCellValue(pagelist.YearsOfIncu == null ? "" : Common.GetInt(pagelist.YearsOfIncu));
            datarow18.GetCell(1).CellStyle = style;
            datarow18.GetCell(3).CellStyle = style;
            HSSFRow datarow19 = (HSSFRow)sheet.GetRow(19);
            datarow19.GetCell(1).SetCellValue(pagelist.SilkwormSpots == null ? "" : Common.GetInt(pagelist.SilkwormSpots));
            datarow19.GetCell(3).SetCellValue(pagelist.SilkwormArea == null ? "" : Common.GetOne(pagelist.SilkwormArea));
            datarow19.GetCell(1).CellStyle = style;
            datarow19.GetCell(3).CellStyle = style;
            HSSFRow datarow20 = (HSSFRow)sheet.GetRow(20);
            datarow20.GetCell(1).SetCellValue(pagelist.QuaternaryFertility == null ? "" : Common.GetInt(pagelist.QuaternaryFertility));
            datarow20.GetCell(3).SetCellValue(pagelist.AnnualTotalFertility == null ? "" : Common.GetInt(pagelist.AnnualTotalFertility));
            datarow20.GetCell(1).CellStyle = style;
            datarow20.GetCell(3).CellStyle = style;
            HSSFRow datarow21 = (HSSFRow)sheet.GetRow(21);
            datarow21.GetCell(1).SetCellValue(pagelist.LargeCocoonNum == null ? "" : Common.GetInt(pagelist.LargeCocoonNum));
            datarow21.GetCell(3).SetCellValue(pagelist.LargeCocoonArea == null ? "" : Common.GetOne(pagelist.LargeCocoonArea));
            datarow21.GetCell(1).CellStyle = style;
            datarow21.GetCell(3).CellStyle = style;
            HSSFRow datarow22 = (HSSFRow)sheet.GetRow(22);
            datarow22.GetCell(1).SetCellValue(pagelist.LaborSavingRearTable == null ? "" : Common.GetInt(pagelist.LaborSavingRearTable));
            datarow22.GetCell(3).SetCellValue(pagelist.GridCluster == null ? "" : Common.GetInt(pagelist.GridCluster));
            datarow22.GetCell(1).CellStyle = style;
            datarow22.GetCell(3).CellStyle = style;
            HSSFRow datarow23 = (HSSFRow)sheet.GetRow(23);
            datarow23.GetCell(1).SetCellValue(pagelist.CocoonStation == null ? "" : Common.GetInt(pagelist.CocoonStation));
            datarow23.GetCell(3).SetCellValue(pagelist.CocoonStationArea == null ? "" : Common.GetOne(pagelist.CocoonStationArea));
            datarow23.GetCell(1).CellStyle = style;
            datarow23.GetCell(3).CellStyle = style;
            HSSFRow datarow24 = (HSSFRow)sheet.GetRow(24);
            datarow24.GetCell(1).SetCellValue(pagelist.SeasonalDryCapacity == null ? "" : Common.GetInt(pagelist.SeasonalDryCapacity));
            datarow24.GetCell(3).SetCellValue(pagelist.CircularDryMachine == null ? "" : Common.GetInt( pagelist.CircularDryMachine));
            datarow24.GetCell(1).CellStyle = style;
            datarow24.GetCell(3).CellStyle = style;
            //HSSFRow datarow26 = (HSSFRow)sheet.GetRow(26);
            //datarow26.GetCell(1).SetCellValue(pagelist.model.ReelingEnterprise == null ? "" : pagelist.model.ReelingEnterprise.ToString());
            //datarow26.GetCell(1).CellStyle = style;
            //HSSFRow datarow27 = (HSSFRow)sheet.GetRow(27);
            //datarow27.GetCell(1).SetCellValue(pagelist.model.ReelingCapacityOne == null ? "" : pagelist.model.ReelingCapacityOne.ToString());
            //datarow27.GetCell(3).SetCellValue(pagelist.model.ReelingCapacityTwo == null ? "" : pagelist.model.ReelingCapacityTwo.ToString());
            //datarow27.GetCell(1).CellStyle = style;
            //datarow27.GetCell(3).CellStyle = style;
            //HSSFRow datarow28 = (HSSFRow)sheet.GetRow(28);
            //datarow28.GetCell(1).SetCellValue(pagelist.model.ReelingCapacityThree == null ? "" : pagelist.model.ReelingCapacityThree.ToString());
            //datarow28.GetCell(3).SetCellValue(pagelist.model.ReelingCapacityFour == null ? "" : pagelist.model.ReelingCapacityFour.ToString());
            //datarow28.GetCell(1).CellStyle = style;
            //datarow28.GetCell(3).CellStyle = style;
            //HSSFRow datarow29 = (HSSFRow)sheet.GetRow(29);
            //datarow29.GetCell(1).SetCellValue(pagelist.model.SilkwormField == null ? "" : pagelist.model.SilkwormField.ToString());
            //datarow29.GetCell(3).SetCellValue(pagelist.model.ThroughPut == null ? "" : pagelist.model.ThroughPut.ToString());
            //datarow29.GetCell(1).CellStyle = style;
            //datarow29.GetCell(3).CellStyle = style;
            //HSSFRow datarow30 = (HSSFRow)sheet.GetRow(30);
            //datarow30.GetCell(1).SetCellValue(pagelist.model.SilkEnterprise == null ? "" : pagelist.model.SilkEnterprise.ToString());
            //datarow30.GetCell(3).SetCellValue(pagelist.model.ProcessedSilkQuilt == null ? "" : pagelist.model.ProcessedSilkQuilt.ToString());
            //datarow30.GetCell(1).CellStyle = style;
            //datarow30.GetCell(3).CellStyle = style;
            //HSSFRow datarow31 = (HSSFRow)sheet.GetRow(31);
            //datarow31.GetCell(1).SetCellValue(pagelist.model.Other == null ? "" : pagelist.model.Other.ToString());
            //datarow31.GetCell(3).SetCellValue(pagelist.model.OtherThroughPut == null ? "" : pagelist.model.OtherThroughPut.ToString());
            //datarow31.GetCell(1).CellStyle = style;
            //datarow31.GetCell(3).CellStyle = style;

            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 导出统计数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream MethodStats(MulNowModel model)
        {
            string FillInYear = model.FillInYear;
            AreaServices areaServices = new AreaServices();
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name == "云南省" ? "云南省_县(市、州)" : areaServices.GetByCodeModel(model.AreaCode).model.Name;//获取当前行政区划名称
            getExcelStatsList(workbook, GetDetailStats(model).model, AreaName);//省级汇总(针对曲线)
            if (model.AreaCode == DefaultKey.ProvinceCode)//判断当前用户是否为省级用户
            {
                AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name;
                model.FillInYear = FillInYear;
                getExcelStatsList(workbook, GetStatsList(model).model, AreaName + "_州市");//省级汇总(针对州市)
                var areaList = areaServices.GetPrefectureList();//获取州市级列表
                foreach (var areaCode in areaList)
                {
                    model.AreaCode = areaCode;
                    model.FillInYear = FillInYear;
                    getExcelStatsList(workbook, GetDetailStats(model).model, areaServices.GetByCodeModel(areaCode).model.Name);
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 返回excel工作簿
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="sheet">子表</param>
        /// <param name="model"></param>
        /// <param name="AreaName">地区名称</param>
        /// <returns></returns>
        public HSSFWorkbook getExcelStatsList(HSSFWorkbook workbook, List<MulProdInfoStatsModel> list, string AreaName)
        {
            FileStream file = null;
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\ExcelStats.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulProdInfoChart") as HSSFSheet;//读取模板所在sheet位置
            //List<VegBasicInfoStatsModel> list = new List<VegBasicInfoStatsModel>();
            //list = null;
            //list = GetDetailStats(model).model;
            #region 县市级表格样式
            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            var styleAreaOne = Common.GetOneAreaCellStyle(templateBook, style);
            var styleAreaInt = Common.GetIntAreaCellStyle(templateBook, style);
            #endregion
            #region 州市级表格样式
            var styleCityOne = Common.GetOneCityCellStyle(templateBook, style);
            var styleCityInt = Common.GetIntCityCellStyle(templateBook, style);

            #endregion
            if (list != null)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 4);
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName.ToString());
                    datarow.CreateCell(1).SetCellValue(list[i].NumTownship == null ? 0 :Convert.ToDouble( list[i].NumTownship));
                    datarow.CreateCell(2).SetCellValue(list[i].NumVillage == null ? 0 : Convert.ToDouble( list[i].NumVillage));
                    datarow.CreateCell(3).SetCellValue(list[i].MulHouseholds == null ? 0 : Convert.ToDouble( list[i].MulHouseholds));
                    datarow.CreateCell(4).SetCellValue(list[i].AnnualSilkwormOne == null ? 0 : Convert.ToDouble( list[i].AnnualSilkwormOne));
                    datarow.CreateCell(5).SetCellValue(list[i].AnnualSilkwormTwo == null ? 0 : Convert.ToDouble( list[i].AnnualSilkwormTwo));
                    datarow.CreateCell(6).SetCellValue(list[i].AnnualSilkwormThree == null ? 0 : Convert.ToDouble( list[i].AnnualSilkwormThree));
                    datarow.CreateCell(7).SetCellValue(list[i].AnnualSilkwormFour == null ? 0 : Convert.ToDouble( list[i].AnnualSilkwormFour));
                    datarow.CreateCell(8).SetCellValue(list[i].TecPersonTotalNum == null ? 0 : Convert.ToDouble( list[i].TecPersonTotalNum));
                    datarow.CreateCell(9).SetCellValue(list[i].TecPersonAllInNum == null ? 0 : Convert.ToDouble( list[i].TecPersonAllInNum));
                    datarow.CreateCell(10).SetCellValue(list[i].ManNum == null ? 0 : Convert.ToDouble( list[i].ManNum));
                    datarow.CreateCell(11).SetCellValue(list[i].WomanNum == null ? 0 : Convert.ToDouble( list[i].WomanNum));
                    datarow.CreateCell(12).SetCellValue(list[i].AgeOne == null ? 0 : Convert.ToDouble( list[i].AgeOne));
                    datarow.CreateCell(13).SetCellValue(list[i].AgeTwo == null ? 0 : Convert.ToDouble( list[i].AgeTwo));
                    datarow.CreateCell(14).SetCellValue(list[i].AgeThree == null ? 0 : Convert.ToDouble( list[i].AgeThree));
                    datarow.CreateCell(15).SetCellValue(list[i].AgeFour == null ? 0 : Convert.ToDouble( list[i].AgeFour));
                    datarow.CreateCell(16).SetCellValue(list[i].EduLevelOne == null ? 0 : Convert.ToDouble( list[i].EduLevelOne));
                    datarow.CreateCell(17).SetCellValue(list[i].EduLeveTwo == null ? 0 : Convert.ToDouble( list[i].EduLeveTwo));
                    datarow.CreateCell(18).SetCellValue(list[i].EduLevelThree == null ? 0 : Convert.ToDouble( list[i].EduLevelThree));
                    datarow.CreateCell(19).SetCellValue(list[i].EduLevelFour == null ? 0 : Convert.ToDouble( list[i].EduLevelFour));
                    datarow.CreateCell(20).SetCellValue(list[i].TitleOne == null ? 0 : Convert.ToDouble( list[i].TitleOne));
                    datarow.CreateCell(21).SetCellValue(list[i].TitleTwo == null ? 0 : Convert.ToDouble( list[i].TitleTwo));
                    datarow.CreateCell(22).SetCellValue(list[i].TitleThree == null ? 0 : Convert.ToDouble( list[i].TitleThree));
                    datarow.CreateCell(23).SetCellValue(list[i].TitleFour == null ? 0: Convert.ToDouble( list[i].TitleFour));
                    datarow.CreateCell(24).SetCellValue(list[i].IncuRoomNum == null ? 0 : Convert.ToDouble( list[i].IncuRoomNum));
                    datarow.CreateCell(25).SetCellValue(list[i].IncuRoomArea == null ? 0.0 : Convert.ToDouble( list[i].IncuRoomArea));
                    datarow.CreateCell(26).SetCellValue(list[i].JiCuiqingPower == null ? 0 : Convert.ToDouble( list[i].JiCuiqingPower));
                    datarow.CreateCell(27).SetCellValue(list[i].YearsOfIncu == null ? 0 : Convert.ToDouble( list[i].YearsOfIncu));
                    datarow.CreateCell(28).SetCellValue(list[i].SilkwormSpots == null ? 0 : Convert.ToDouble( list[i].SilkwormSpots));
                    datarow.CreateCell(29).SetCellValue(list[i].SilkwormArea == null ? 0.0 : Convert.ToDouble( list[i].SilkwormArea));
                    datarow.CreateCell(30).SetCellValue(list[i].QuaternaryFertility == null ? 0 : Convert.ToDouble( list[i].QuaternaryFertility));
                    datarow.CreateCell(31).SetCellValue(list[i].AnnualTotalFertility == null ? 0 : Convert.ToDouble( list[i].AnnualTotalFertility));
                    datarow.CreateCell(32).SetCellValue(list[i].LargeCocoonNum == null ? 0 : Convert.ToDouble( list[i].LargeCocoonNum));
                    datarow.CreateCell(33).SetCellValue(list[i].LargeCocoonArea == null ? 0.0 : Convert.ToDouble( list[i].LargeCocoonArea));
                    datarow.CreateCell(34).SetCellValue(list[i].LaborSavingRearTable == null ? 0.0 : Convert.ToDouble( list[i].LaborSavingRearTable));
                    datarow.CreateCell(35).SetCellValue(list[i].GridCluster == null ? 0 : Convert.ToDouble( list[i].GridCluster));
                    datarow.CreateCell(36).SetCellValue(list[i].CocoonStation == null ? 0 : Convert.ToDouble( list[i].CocoonStation));
                    datarow.CreateCell(37).SetCellValue(list[i].CocoonStationArea == null ? 0.0 : Convert.ToDouble( list[i].CocoonStationArea));
                    datarow.CreateCell(38).SetCellValue(list[i].SeasonalDryCapacity == null ? 0.0 : Convert.ToDouble( list[i].SeasonalDryCapacity));
                    datarow.CreateCell(39).SetCellValue(list[i].CircularDryMachine == null ? 0 : Convert.ToDouble( list[i].CircularDryMachine));
                    if (CustomFun.IsCity(list[i].AreaCode))
                    {//省级和州市级
                        for (int j = 0; j < 40; j++)
                        {
                            datarow.GetCell(j).CellStyle = styleCityInt;
                            if(j==25||j==29||j==34||j==35||j==37||j==38)
                            {
                                datarow.GetCell(j).CellStyle = styleCityOne;
                            }
                        }
                    }
                    else//县级
                    {
                        for (int j = 0; j < 40; j++)
                        {
                            datarow.GetCell(j).CellStyle = styleAreaInt;
                            if (j == 25 || j == 29 || j == 34 || j == 35 || j == 37 || j == 38)
                            {
                                datarow.GetCell(j).CellStyle = styleAreaOne;
                            }
                        }
                    }
                }
                sheet.CopyTo(workbook, AreaName, true, true);
            }
            return workbook;
        }
    }
}